#!/usr/bin/python
# -*- coding: UTF-8 -*-

from datetime import datetime,date,timedelta
import time
from xlwt import *
import pymysql
import pandas as pd

conn = pymysql.connect(
    host = "192.168.1.139",
    port = 3306,
    user = "sp1234",
    passwd = "fai",
    db = "jddb2"
)

cur = conn.cursor()
# cur.execute("select fname,cunit from tb_dc")
# result = cur.fetchall()
#
# df_result = pd.DataFrame(list(result),columns = ["fname","cunit"])
#
# print(df_result.shape)
# print(df_result.head())


stmp = time.localtime()
now_time = time.strftime('%Y/%m/%d',stmp)
now_time = datetime.strptime(now_time, '%Y/%m/%d')
yesterday = (date.today() + timedelta(days=-7)).strftime("%Y/%m/%d")
yesterday = datetime.strptime(yesterday, '%Y/%m/%d')
cur.execute("select hcount,dc_id from tb_gd WHERE create_time > yesterday and create_time < now_time and is_delete = '0'")
result = cur.fetchall()
df_result = pd.DataFrame(list(result),columns = ["hcount","dc_id"])
print(df_result.shape)
print(df_result.head())

useres = Users.objects.only("first_name").filter(is_active=True,idcn_id=idcn_id)
useres1 = Users.objects.only("first_name").filter(is_active=True)
idcn = IdcN.objects.only("id").filter(is_delete=False)
gdes = Gd.objects.only("hcount","dc_id").filter(is_delete=False)
dces = DesCond.objects.only("fname").filter(is_delete=False)



gdes = gdes.filter(update_time__range=(yesterday, now_time))

ws = Workbook(encoding='utf-8')
for i in idcn:
    hz = 0
    sz = 0
    id = i.id
    rs = 0
    useres2 = useres1.filter(idcn_id=id)
    for io in useres2:
        rs += 1
    gdes1 = gdes.filter(idcn_id=id)
    if gdes1:
        w = ws.add_sheet(str(i.name))
        w.write(0, 0, "工单类型")
        w.write(0, 1, "工单数量")
        w.write(0, 2, "工单耗时")
        excel_row = 1
        for obj in dces:
            name = obj.fname
            dc_id = obj.id
            n = 0
            h = 0
            gdes2 = gdes1.filter(dc_id=dc_id)

            for m in gdes2:
                n = n + 1
                h = h + m.hcount
            hz = hz + n
            sz = sz + h
            w.write(excel_row, 0, name)
            w.write(excel_row, 1, n)
            w.write(excel_row, 2, h)
            excel_row += 1
        w.write(excel_row, 0 ,'合计数量')
        w.write(excel_row, 1, hz)
        w.write(excel_row, 2, sz)
        w.write(excel_row+1, 0, '人均数量')
        w.write(excel_row+1, 1, int(hz/rs))
        w.write(excel_row+1, 2, int(sz/rs))

ws.save('test2.xls')
print('test2创建成功')

